package jinyilw.common.database;

import jinyilw.common.Operator;
import jinyilw.common.OperatorSets;
import jinyilw.common.ReflectTools;
import jinyilw.common.TextTools;
import jinyilw.common.log.LogTools;

import java.io.Reader;
import java.lang.reflect.Field;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class JdbcTools
{
	private DBParameter parameter;
	private Connection connection;
	private Statement statement;
	private final OperatorSets<Object> brokenOperators = new OperatorSets<>();
	private final OperatorSets<Object> reConnectOperators = new OperatorSets<>();
	public JdbcTools(DBParameter parameter)
	{
		this.parameter = parameter;
		connection = getConnection(parameter);
	}

	public static JdbcTools getTools(String driver, String url, String user,
			String password)
	{
		return new JdbcTools(new DBParameter(driver, url, user, password));
	}

	public static JdbcTools getTools(DBParameter parameter)
	{
		return new JdbcTools(parameter);
	}

	public static Connection getConnection(DBParameter param)
	{
		if (param == null)
			return null;
		return getConnection(param.driver, param.url, param.username,
				param.password);
	}

	public static Connection getConnection(String driver, String url,
			String user, String password)
	{
		long t = System.currentTimeMillis();
		Connection connection;
		try
		{
			Class.forName(driver);
			connection = DriverManager.getConnection(url, user, password);
		} catch (Exception e)
		{
			LogTools.debug("数据库连接失败!", e);
			return null;
		}
		LogTools.info("数据库连接成功! 用时:" + (System.currentTimeMillis() - t));
		return connection;
	}

	public static ResultSet executeScript(Connection conn, Reader reader)
			throws SQLException
	{
		if (conn == null || reader == null)
			return null;
		Statement stat = conn.createStatement();
		ResultSet rs = null;
		ScriptReader r = new ScriptReader(reader);
		while (true)
		{
			String sql = r.readStatement();
			if (sql == null)
				break;
			if (TextTools.isBlank(sql))
				continue;
			boolean resultSet = stat.execute(sql);
			if (resultSet)
			{
				if (rs != null)
				{
					rs.close();
					rs = null;
				}
				rs = stat.getResultSet();
			}
		}
		r.close();
		return rs;
	}

	public void addBrokenOperator(Operator<Object> operator)
	{
		brokenOperators.add(operator);
	}

	public void addBrokenRunner(Runnable runner)
	{
		if (runner != null)
			brokenOperators.add(o ->
			{
				new Thread(runner).start();
			});
	}

	public void addReConnectOperator(Operator<Object> operator)
	{
		reConnectOperators.add(operator);
	}

	public void addReConnectRunner(Runnable runner)
	{
		if (runner != null)
			reConnectOperators.add(o ->
			{
				new Thread(runner).start();
			});
	}

	public void reConnectInThread()
	{
		new Thread(this::reConnect).start();
	}

	public synchronized boolean reConnect()
	{
		if (isConnected())
			return true;
		connection = getConnection(parameter);
		if (connection != null)
		{
			reConnectOperators.operate(this);
			return true;
		}
		return false;
	}

	public void setParameter(DBParameter param)
	{
		if (parameter != param)
		{
			parameter = param;
			releaseConnect();
			reConnect();
		}
	}

	public void setConnection(Connection connection)
	{
		this.connection = connection;
	}

	public Connection getConnection()
	{
		return connection;
	}

	public synchronized boolean isConnected()
	{
		if (connection == null)
			return false;
		try
		{
			if (connection.isClosed())
			{
				connection = null;
				statement = null;
				brokenOperators.operate(this);
				return false;
			}
			connection.prepareStatement("");
		} catch (SQLException e)
		{
			LogTools.debug("数据库未连接！", e);
			if (e.getMessage().indexOf("broken") > 0)
				releaseConnect();
			return false;
		}
		return true;
	}

	public void releaseConnect()
	{
		Connection con = connection;
		connection = null;
		if (con != null)
		{
			brokenOperators.operate(this);
			try
			{
				if (statement != null)
					statement.close();
				con.close();
			} catch (SQLException e)
			{
				LogTools.debug("关闭数据库出错", e);
			} finally
			{
				statement = null;
			}
		}
	}

	public void releaseConnectInThread()
	{
		new Thread(this::releaseConnect).start();
	}

	private Statement getStatement() throws SQLException
	{
		if (statement == null)
			statement = connection.createStatement();
		return statement;
	}

	public boolean executeSql(String sql)
	{
		if (isConnected())
			try
			{
				return getStatement().execute(sql);
			} catch (SQLException e)
			{
				LogTools.debug("查询出错：" + sql, e);
			}
		return false;
	}

	public int executeUpdateSql(String sql)
	{
		if (isConnected())
			try
			{
				return getStatement().executeUpdate(sql);
			} catch (SQLException e)
			{
				LogTools.debug("查询出错：" + sql, e);
			}
		return 0;
	}

	public ResultSet executeQuerySql(String sql)
	{
		if (isConnected())
			try
			{
				return getStatement().executeQuery(sql);
			} catch (SQLException e)
			{
				LogTools.debug("查询出错：" + sql, e);
			}
		return null;
	}

	public ResultSet executeScript(Reader reader)
	{
		if (isConnected())
			try
			{
				return executeScript(connection, reader);
			} catch (SQLException e)
			{
				LogTools.debug("查询出错：" + reader.toString(), e);
			}
		return null;
	}

	/**
	 * 增加 删除 修改
	 */
	public boolean updateByPreparedStatement(String sql, Object... params)
	{
		if (!isConnected())
			return false;
		int result = -1;
		PreparedStatement preparedStatement;
		try
		{
			preparedStatement = connection.prepareStatement(sql);
			int index = 1;
			for (Object o : params)
				preparedStatement.setObject(index++, o);
			result = preparedStatement.executeUpdate();
		} catch (SQLException e)
		{
			LogTools.debug("查询出错：" + sql, e);
		}
		return result > 0;
	}

	/**
	 * 查询单条记录
	 */
	public Map<String, Object> findSimpleResult(String sql, Object... params)
	{
		if (!isConnected())
			return null;
		Map<String, Object> map = new HashMap<>();
		try
		{
			PreparedStatement preparedStatement = connection
					.prepareStatement(sql);
			int index = 1;
			for (Object o : params)
				preparedStatement.setObject(index++, o);
			ResultSet resultSet = preparedStatement.executeQuery();
			ResultSetMetaData metaData = resultSet.getMetaData();
			int l = metaData.getColumnCount();
			if (resultSet.next())
				for (int i = 0; i < l; i++)
				{
					String name = metaData.getColumnName(i + 1);
					Object value = resultSet.getObject(name);
					if (value == null)
						value = "";
					map.put(name, value);
				}
		} catch (SQLException e)
		{
			LogTools.debug("查询出错：" + sql, e);
		}
		return map;
	}

	/**
	 * 查询多条记录
	 */
	public List<Map<String, Object>> findMoreResult(String sql,
			Object... params)
	{
		if (!isConnected())
			return null;
		List<Map<String, Object>> list = new ArrayList<>();
		try
		{
			PreparedStatement preparedStatement = connection
					.prepareStatement(sql);
			int index = 1;
			for (Object o : params)
				preparedStatement.setObject(index++, o);
			ResultSet resultSet = preparedStatement.executeQuery();
			ResultSetMetaData metaData = resultSet.getMetaData();
			int l = metaData.getColumnCount();
			while (resultSet.next())
			{
				Map<String, Object> map = new HashMap<>();
				for (int i = 0; i < l; i++)
				{
					String name = metaData.getColumnName(i + 1);
					Object value = resultSet.getObject(name);
					if (value == null)
						value = "";
					map.put(name, value);
				}
				list.add(map);
			}
		} catch (SQLException e)
		{
			LogTools.debug("查询出错：" + sql, e);
		}
		return list;
	}

	/**
	 * 通过反射机制创建查询类
	 */
	public <T> T findSimpleRefResult(String sql, Class<T> cls,
			String[] memberNames, Object... params)
	{
		if (!isConnected())
			return null;
		T resultObject = null;
		try
		{
			PreparedStatement preparedStatement = connection
					.prepareStatement(sql);
			int index = 1;
			for (Object o : params)
				preparedStatement.setObject(index++, o);
			ResultSet resultSet = preparedStatement.executeQuery();
			if (resultSet.next() && memberNames != null)
			{
				resultObject = cls.newInstance();
				for (String name : memberNames)
				{
					// System.out.println(name);
					Object value = resultSet.getObject(name);
					if (value == null)
						continue;
					Field field = cls.getDeclaredField(name);
					if (field.getType() == int.class)
						value = Integer.parseInt(value.toString());
					else if (field.getType() == double.class)
						value = Double.parseDouble(value.toString());
					field.setAccessible(true);
					field.set(resultObject, value);
				}
			}
		} catch (Exception e)
		{
			LogTools.debug("查询出错：" + sql, e);
		}
		return resultObject;
	}

	/**
	 * 通过反射机制创建查询类
	 */
	public <T> T findSimpleRefResult(String sql, Class<T> cls, Object... params)
	{
		if (!isConnected())
			return null;
		List<String> names = ReflectTools.getNoneStaticFieldNames(cls);
		return findSimpleRefResult(sql, cls,
				names.toArray(new String[names.size()]), params);
	}

	public <T> List<T> findMoreRefResult(String sql, Class<T> cls,
			String[] memberNames, Object... params)
	{
		if (!isConnected())
			return null;
		List<T> list = new ArrayList<T>();
		try
		{
			PreparedStatement preparedStatement = connection
					.prepareStatement(sql);
			int index = 1;
			for (Object o : params)
				preparedStatement.setObject(index++, o);
			ResultSet resultSet = preparedStatement.executeQuery();
			if (memberNames != null)
				while (resultSet.next())
				{
					T resultObject = cls.newInstance();
					for (String name : memberNames)
					{
						Object value = resultSet.getObject(name);
						if (value == null)
							continue;
						Field field = cls.getDeclaredField(name);
						if (field.getType() == int.class)
							value = Integer.parseInt(value.toString());
						else if (field.getType() == double.class)
							value = Double.parseDouble(value.toString());
						field.setAccessible(true);
						field.set(resultObject, value);
					}
					list.add(resultObject);
				}
		} catch (Exception e)
		{
			LogTools.debug("查询出错：" + sql, e);
		}
		return list;
	}

	public <T> List<T> findMoreRefResult(String sql, Class<T> cls,
			Object... params)
	{
		if (!isConnected())
			return null;
		List<String> names = ReflectTools.getNoneStaticFieldNames(cls);
		return findMoreRefResult(sql, cls,
				names.toArray(new String[names.size()]), params);
	}

}
